
//raw data pulled from oracle database
use "$data_in/Drug_Tests", clear
replace s_coll_dt = dofc(s_coll_dt)
format s_coll_dt %d
rename (s_coll_dt drg_tstng_rslt_cd illic_drg_cd) (date_test drug illegal)
drop if drug == "DISCR"
gen test_positive = drug != "NEG"
gen positive_illegal = illegal != "0"

gen opiate = inlist(drug, "COD", "HYCOD", "HYMOR", "MOR", "OXCOD", "OXMOR", "6AM")
gen heroine = drug == "6AM"
gen  benzo = inlist(drug, "AHAL", "LORA", "NORD", "OXAZ", "TEMA")
gen MJ = inlist(drug, "J018C", "J073C", "SYCAN", "THC", "U144C")
gen other = inlist(drug, "COC", "MAM22", "PCP", "STE", "ABCHM", "DAMP", "DMETH", "MDA", "MDMA")
gen opiate_prescribed = opiate == 1 & positive_illegal == 0
gen opiate_illegal = opiate == 1 & positive_illegal == 1



collapse (max) opiate benzo heroine MJ other test_positive  opiate_prescribed opiate_illegal, by(pid_pde date_test) fast




bys pid_pde (date_test): gen test_sequence = _n
summ test_sequence
local max_r `r(max)'
reshape wide date_test opiate benzo MJ other heroine test_positive opiate_prescribed opiate_illegal , i(pid_pde) j(test_sequence)
merge 1:m pid_pde using "$data_out/provider_propensity", keep(2 3) nogen


gen tested_365 = 0
forval i = 1/`max_r' {
qui replace tested_365 = 1 if  inrange(date_test`i', encdate, encdate + 365) & !mi(date_test`i')
}

keep if tested_365 == 1

foreach result in heroine test_positive opiate_prescribed opiate_illegal opiate benzo MJ other {
gen ua_`result' = 0
forval i = 1/`max_r' {
qui recode `result'`i' .=0
qui replace ua_`result' = 1 if `result'`i' == 1 & inrange(date_test`i', encdate, encdate + 365) & !mi(date_test`i')
}
}
save "$data_out/Drug_Tests", replace
